/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import Exceptions.InvalidLoginException;
import Model.UserBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.List;
import java.util.ArrayList;

/**
 *
 * @author amkreuz
 */
public class UserDAO {

    private static Connection DBConn;

    public static void init() throws SQLException {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException e) {
            System.err.println(e.getMessage());
            System.exit(0);
        }

        DBConn = DriverManager.getConnection("jdbc:derby://localhost:1527/ChoreBoard");

    }

    public static void terminate() throws SQLException {

        DBConn.close();

    }

    public static void createUser(UserBean newProfile) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("insert into PERSON values ('"
                + newProfile.getUserName() + "','"
                + newProfile.getPassword() + "',"
                + null + "," + 0 + "," + false
                + ")");

    }

    public static void updateUserBean(UserBean aProfile) throws SQLException {
        Statement stmt = DBConn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from person where username='" + aProfile.getUserName() + "'");
        rs.next();
        String p = rs.getString("points");
        if (p == null) {
            aProfile.setPoints(0);
        } else {
            aProfile.setPoints(Integer.parseInt(p));
        }
        aProfile.setHouseName(rs.getString("household"));
        aProfile.setIsAdmin(Boolean.parseBoolean(rs.getString("houseadmin")));
    }

    public static String getPassword(UserBean bean) throws SQLException {
        Statement stmt = DBConn.createStatement();
        String result;

        ResultSet rs = stmt.executeQuery("select password from person where username='" + bean.getUserName() + "'");
        rs.next();
        result = rs.getString("PASSWORD");

        return result;
    }

    public static void setPassword(UserBean user) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("update person set PASSWORD='" + user.getPassword() + "' where USERNAME='" + user.getUserName() + "'");

    }

    public static List<String> getAllUserNames() throws SQLException {
        ArrayList<String> allNames = new ArrayList<String>();
        Connection DBConn = DriverManager.getConnection("jdbc:derby://localhost:1527/ChoreBoard");
        Statement stmt = DBConn.createStatement();
        ResultSet rs = stmt.executeQuery("select username from person");
        while (rs.next()) {
            String name = rs.getString("username");
            allNames.add(name);
        }
        return allNames;
    }


    public static void deleteAccount(UserBean user) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("delete from PERSON where USERNAME='" + user.getUserName() + "'");
    }

    public static void joinHousehold(UserBean bean) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("update person set household='" + bean.getHouseName() + "'" + "where username='" + bean.getUserName() + "'");
    }
    
    public static void save(UserBean user) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("update person set household='"+user.getHouseName()+"', houseadmin="+user.isIsAdmin()+", password='"+user.getPassword()+"', points="+user.getPoints()+" where username='"+user.getUserName()+"'");
    }
    
    public static void leaveHousehold(UserBean bean)throws SQLException{
        Statement stmt = DBConn.createStatement();
        
        stmt.executeUpdate("update PERSON set HOUSEHOLD=NULL, POINTS=0, HOUSEADMIN=FALSE where USERNAME='"+bean.getUserName()+"'");
    }
    
    public static ArrayList<UserBean> getUsers(String house) throws SQLException {
        ArrayList<UserBean> users = new ArrayList<>();
        
        Statement stmt = DBConn.createStatement();
        
        ResultSet rs = stmt.executeQuery("select * from person where household='"+house+"'");
        
        while (rs.next()) {
            UserBean user = new UserBean();
            user.setHouseName(house);
            user.setUserName(rs.getString("username"));
            
            user.setPoints(rs.getInt("points"));
            users.add(user);
        }
        
        return users;
    }
}